package test;

import cn.edu.sdjzu.xg.bysj.domain.School;


import java.sql.*;

public class DBTest2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动程序
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //url，数据库名称
        String url = "jdbc:sqlserver://localhost:1433;databaseName=201902104014";
        //创建Connection对象，根据用户名和密码与数据库进行连接
        Connection connection = DriverManager.getConnection(url, "sa", "1");
        //调用find方法，把返回值打印到控制台上
        System.out.println(DBTest.find(2,connection));
        //调用delete方法，把返回值打印到控制台上
        System.out.println(DBTest.delete(3,connection));
        //关闭connection
        connection.close();
    }
    public static School find(int id, Connection conn) throws SQLException {
        //对应School表，创建四个字段
        int int_id = 0;
        String description=null;
        String no=null;
        String remarks=null;
        //在连接上创建语句盒子对象
        Statement stmt = conn.createStatement();
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = stmt.executeQuery("select * from school where id = "+id);
        //若结果集仍然有下一条记录，则执行循环体
        while (resultSet.next()){
            //给各个字段赋值
            int_id = resultSet.getInt("id");
            description = resultSet.getString("description");
            no = resultSet.getString("no");
            remarks = resultSet.getString("remarks");
        }
        //创建School对象并为其赋值
        School school = new School(int_id,description,no,remarks);
        return school;
    }
    public static boolean delete(int id, Connection conn) throws SQLException {
        //连接上创建语句盒子对象
        Statement stmt = conn.createStatement();
        //创建sql语句
        String deleteSchool_sql = "DELETE FROM school WHERE id ="+ id;
        //获取删除的记录行数
        int affectedRowNum = stmt.executeUpdate(deleteSchool_sql);
        if(affectedRowNum==1){
            return true;
        }
        return false;
    }
}